---
title: Record APIs
---

import { Aside, Code } from "@astrojs/starlight/components";
import { Tabs, TabItem } from '@astrojs/starlight/components';
import { githubCodeReference } from '@/lib/github';

import { apiPath, recordApiNamePlaceholder, recordApiIdPlaceholder } from "./_record_apis.ts";

TrailBase's _Record APIs_ allow you to easily configure endpoints exposing your
`TABLE`s and `VIEW`s though **type-safe** restful
**C**reate**R**ead**U**pdate**D**elete operations, composite list queries and
even realtime change subscriptions.

For your data to be exposed via _Record APIs_:

- `TABLE`s and `VIEW`s need to be `STRICT`ly[^1] typed to guarantee type-safety
  all the way from your DB records, over JSON schemas, to your client-side
  language bindings[^2].
- To allow for stable sorting and thus efficient cursor-based pagination,
  `TABLE`s are required to have either `INTEGER`, UUIDv4 or UUIDv7 primary key
  columns.

## Configuration

Record APIs can be configured via the admin dashboard or direclty in
TrailBase's configuration file.

An example API setup for managing user profiles:

```json
record_apis: [
  {
    name: "profiles"
    table_name: "profiles"
    conflict_resolution: REPLACE
    acl_authenticated: [READ, CREATE]
    create_access_rule: "_REQ_.user = _USER_.id"
  },
]
```

A quick explanation:

* The `name` needs to be unique. It's what is used to access the API via
  <code>{apiPath({name:"name", prefix:"http://<host>"})}</code>
* `table_name` references the `TABLE` or `VIEW` that is being exposed.
* `conflict_resolution` declares what should happen if a newly created record is
  conflicting with an existing one.
* `autofill_missing_user_id_column` lets you omit fields for columns with a foreign
  key relationship to `_user(id)`. The field will then be filled with the
  credentials of the authenticated user. In most cases, this should probably be off, this
  only useful if you cannot explicitly provide the user id yourself, e.g. in a
  static HTML form.
* `acl_world` and `acl_authenticated` define that anyone can read avatars but
  only authenticated users can modify them. The following `access_rules` further narrow
  mutations to records where the `user` column (or request field for insertions)
  match. In other words, user X cannot modify user Y's avatar.

Note that and `TABLE`/`VIEW` can be exposed multiple times as different APIs
with different properties.


### Permissions

Access can be controlled through combination of a simple ACL-based system (a
matrix of who and what) and custom SQL access rules of the nature:
`f(req, user, row) -> bool`.
Generally, the ACLs are checked first and then the access rules are evaluated
when present.

For example, to validate that the requester provided a secret key and is member
of a group `'mygroup'`:

```sql
(_REQ_.secret = 'foo' AND EXISTS(
  SELECT 1 FROM groups
  WHERE
    groups.member = _USER_.id
    AND groups.name = 'mygroup'
  ))
```

* `_REQ_` is an injected sub-query containing a user request's fields. It is
  available in the access rules for `CREATE` and `UPDATE` operations.
  Fields that were either absent in the request or explicitly passed as `null`,
  will be `NULL`.
* To check for the presence of a specific field in a `CREATE` or `UPDATE`
  request, you can use `'field' IN _REQ__FIELDS_`. Note that `_REQ_FIELDS_` may
  only contain fields with a corresponding column in the underlying `TABLE` or
  `VIEW`.
* Similarly, `_ROW_` is a sub-query of the target record. It is available in
  the access rules for `READ`, `UPDATE`, and `DELETE` operations.
* Lastly, `_USER_.id` references the id of the currently authenticated user and
  `NULL` otherwise.

Independently, you can use `VIEW`s to filter which rows and columns of
your `TABLE`s should be accessible.

#### Building Access Groups and Capabilities

As hinted at by the example above, the SQL access rules can be used to
build higher-level access protection such as group ACLs or capabilities.
What makes the most sense in your case, is very application dependent.
The `<repo>/examples/blog` has an "editor" group to control who can write blog
posts.

Somewhat on a tangent, group and capability tables can themselves be exposed
via Record APIs.
This can be used to programmatically manage permissions, e.g. for building a
moderation dashboard.
When exposing authorization primitives, make sure the permissions are
appropriately tight to avoid permission escalations.

### `VIEW`-based APIs

`VIEW`s can support a variety of use-cases, e.g.: read-only APIs on a subset of
columns, APIs exposing complex joins across many tables, computed values across
many columns, etc.
Note that computed columns require a top-level `CAST` expression for TrailBase
to determine the resulting type and satisfy the type-safety requirements for
record APIs, e.g.:


export const viewExample = `
CREATE VIEW profile_view AS
  SELECT
    p.*,
    -- The CAST is needed to determine the type of the result.
    CAST(IIF(editors.user IS NULL, FALSE, TRUE) AS BOOLEAN) AS editor
  FROM profiles AS p
    LEFT JOIN editors ON p.user = editors.user;
`;

<Code lang="sql" code={viewExample} mark={["CAST(", "AS BOOLEAN)"]} />


### Write-only columns

Columns with names starting with an underscore can be written on insert or
updated but are hidden during read/list operations.
This is meant as a convenient convention to allow for internal data fields, e.g
hiding the record owner in an otherwise public data set or hiding a user's
internal credit rating from their profile.
A similar effect could otherwise be achieved by only allowing create/update
operations on the table and exposing a subset of columns through a readable
`VIEW`.
However, this would lead to different API endpoints for read vs create/update.

<Aside type="note" title="Unhiding">
  Note that views can also be used to rename columns and thus expose hidden columns
  in a read-only fashion.
</Aside>

## Access

After setting up your API, TrailBase will expose the following main endpoints[^3]:

* **C**reate: <code>POST {apiPath({name: recordApiNamePlaceholder})}</code>
* **R**ead: <code>GET {apiPath({name: recordApiNamePlaceholder, suffix: recordApiIdPlaceholder})}</code>
* **U**pdate: <code>PATCH {apiPath({name: recordApiNamePlaceholder, suffix: recordApiIdPlaceholder})}</code>
* **D**elete: <code>DELETE {apiPath({name: recordApiNamePlaceholder, suffix: recordApiIdPlaceholder})}</code>
* List: <code>GET {apiPath({name: `${recordApiNamePlaceholder}?<search_params>`})}</code>
* Change Subscriptions: <br/><code>GET {apiPath({name: recordApiNamePlaceholder, suffix: `subscribe/[*|${recordApiIdPlaceholder}]`})}</code>
* Schema: <code>GET {apiPath({name: recordApiNamePlaceholder, suffix: "schema"})}</code>

All of the endpoints accept requests that are JSON encoded, url-encoded, or
`multipart/form-data` encoded, which makes them accessible via rich client-side
applications, progressive web apps, and static HTML forms alike.

### Create

The create endpoint lets you insert new records and potentially override
existing ones depending on conflict resolution strategy.

import createDartCode from "@examples/record_api_dart/lib/src/create.dart?raw";
import createTsCode from "@examples/record_api_ts/src/create.ts?raw";
import createSwiftCode from "@examples/record_api_swift/Sources/RecordApiDocs/Create.swift?raw";
import createKotlinCode from "@examples/record_api_kotlin/src/main/kotlin/Create.kt?raw";
import createDotnetCode from "@examples/record_api_dotnet/Create.cs?raw";
import createRustCode from "@examples/record_api_rs/src/create.rs?raw";
import createGoCode from "@examples/record_api_go/create.go?raw";
import createPyCode from "@examples/record_api_py/record_api_py/create.py?raw";
import createCurlCode from "@examples/record_api_curl/create.sh?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={createTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={createDartCode} />
  </TabItem>

  <TabItem label="Swift">
    <Code lang="swift" code={createSwiftCode} />
  </TabItem>

  <TabItem label="Kotlin">
    <Code lang="kotlin" code={createKotlinCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={createDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={createRustCode} />
  </TabItem>

  <TabItem label="Go">
    <Code lang="go" code={createGoCode} />
  </TabItem>

  <TabItem label="Python">
    <Code lang="python" code={createPyCode} />
  </TabItem>

  <TabItem label="curl">
    <Code lang="sh" frame="code" code={createCurlCode} />
  </TabItem>
</Tabs>


### Read

The read endpoint lets you read specific records given their id.

import readDartCode from "@examples/record_api_dart/lib/src/read.dart?raw";
import readTsCode from "@examples/record_api_ts/src/read.ts?raw";
import readSwiftCode from "@examples/record_api_swift/Sources/RecordApiDocs/Read.swift?raw";
import readKotlinCode from "@examples/record_api_kotlin/src/main/kotlin/Read.kt?raw";
import readDotnetCode from "@examples/record_api_dotnet/Read.cs?raw";
import readRustCode from "@examples/record_api_rs/src/read.rs?raw";
import readGoCode from "@examples/record_api_go/read.go?raw";
import readPyCode from "@examples/record_api_py/record_api_py/read.py?raw";
import readCurlCode from "@examples/record_api_curl/read.sh?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={readTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={readDartCode} />
  </TabItem>

  <TabItem label="Swift">
    <Code lang="swift" code={readSwiftCode} />
  </TabItem>

  <TabItem label="Kotlin">
    <Code lang="kotlin" code={readKotlinCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={readDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={readRustCode} />
  </TabItem>

  <TabItem label="Go">
    <Code lang="go" code={readGoCode} />
  </TabItem>

  <TabItem label="Python">
    <Code lang="python" code={readPyCode} />
  </TabItem>

  <TabItem label="curl">
    <Code lang="sh" frame="code" code={readCurlCode} />
  </TabItem>
</Tabs>

### Update

The update endpoint lets you modify, i.e. partially update, existing records given their id

import updateDartCode from "@examples/record_api_dart/lib/src/update.dart?raw";
import updateTsCode from "@examples/record_api_ts/src/update.ts?raw";
import updateSwiftCode from "@examples/record_api_swift/Sources/RecordApiDocs/Update.swift?raw";
import updateKotlinCode from "@examples/record_api_kotlin/src/main/kotlin/Update.kt?raw";
import updateDotnetCode from "@examples/record_api_dotnet/Update.cs?raw";
import updateRustCode from "@examples/record_api_rs/src/update.rs?raw";
import updateGoCode from "@examples/record_api_go/update.go?raw";
import updatePyCode from "@examples/record_api_py/record_api_py/update.py?raw";
import updateCurlCode from "@examples/record_api_curl/update.sh?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={updateTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={updateDartCode} />
  </TabItem>

  <TabItem label="Swift">
    <Code lang="swift" code={updateSwiftCode} />
  </TabItem>

  <TabItem label="Kotlin">
    <Code lang="kotlin" code={updateKotlinCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={updateDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={updateRustCode} />
  </TabItem>

  <TabItem label="Go">
    <Code lang="go" code={updateGoCode} />
  </TabItem>

  <TabItem label="Python">
    <Code lang="python" code={updatePyCode} />
  </TabItem>

  <TabItem label="curl">
    <Code lang="sh" frame="code" code={updateCurlCode} />
  </TabItem>
</Tabs>

### Delete

import deleteDartCode from "@examples/record_api_dart/lib/src/delete.dart?raw";
import deleteTsCode from "@examples/record_api_ts/src/delete.ts?raw";
import deleteSwiftCode from "@examples/record_api_swift/Sources/RecordApiDocs/Delete.swift?raw";
import deleteKotlinCode from "@examples/record_api_kotlin/src/main/kotlin/Delete.kt?raw";
import deleteDotnetCode from "@examples/record_api_dotnet/Delete.cs?raw";
import deleteRustCode from "@examples/record_api_rs/src/delete.rs?raw";
import deleteGoCode from "@examples/record_api_go/delete.go?raw";
import deletePyCode from "@examples/record_api_py/record_api_py/delete.py?raw";
import deleteCurlCode from "@examples/record_api_curl/delete.sh?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={deleteTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={deleteDartCode} />
  </TabItem>

  <TabItem label="Swift">
    <Code lang="swift" code={deleteSwiftCode} />
  </TabItem>

  <TabItem label="Kotlin">
    <Code lang="kotlin" code={deleteKotlinCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={deleteDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={deleteRustCode} />
  </TabItem>

  <TabItem label="Go">
    <Code lang="go" code={deleteGoCode} />
  </TabItem>

  <TabItem label="Python">
    <Code lang="python" code={deletePyCode} />
  </TabItem>

  <TabItem label="curl">
    <Code lang="sh" frame="code" code={deleteCurlCode} />
  </TabItem>
</Tabs>

The delete endpoints lets you remove a record given its id.


### List: Filter, Sort and Paginate

Using the <code>GET {apiPath({name: `${recordApiNamePlaceholder}?<params>`})}</code> endpoint and given
sufficient permissions one can query records based the given `read_access_rule`
and query parameters.

Parameters:

* Pagination can be controlled via the following query parameters:
  * `limit=N`, with a built-in default of 50 and a hard limit of 1024 to avoid abuse.
  * `cursor=<primary key>` to offset into results using a cursor. Significantly
    less expensive than `OFFSET`-based pagination.
  * `offset=N` to offset into results.
  * `count=true` will yield a `total_count` of records in the result. This can
    be used together with `limit` and `cursor` to build pagination UIs.
* Ordering can be controlled using the `order=[[+-]?<column_name>]+` parameter, e.g.
  `order=created,-rank`, which sorts records based on their `created` column in
  ascending order first (same as "+") and subsequently in descending order by
  their `rank` column due to the minus prefix.
* Filtering can be controlled by passing one or more
  `filter[<column_name>][op]=<value>` parameters.
  For example, `filter[revenue][$gt]=0` would list records with a positive `revenue` only.
  Multiple filters on the same column are supported and combined with AND logic,
  e.g. `filter[date][$gte]=2025-01-01&filter[date][$lte]=2025-12-31` for date ranges.
* Supported operators are:
  * **$eq**: equal, which is also the default if no explicit operator is
    specified, i.e. `?success[$eq]=TRUE` and `?success=TRUE` are identical.
  * **$ne**: not equal
  * **$gte**: greater-than-equal
  * **$gt**: greater-than
  * **$lte**: less-than-equal
  * **$lt**: less-than
  * **$is**: is null or not null, i.e. `?col[$is]=NULL` or `?col[$is]=!NULL`, respectively
  * **$like**: SQL `LIKE` operator, e.g. `?col[$like]=%something%`
  * **$re**: SQL `REGEXP` operator, e.g. `?col[$re]=^something$`
* Parent records, i.e. records pointed to by foreign key columns, can be
  expanded using the `?expand=<col0>,<col`>` parameter, if the respective columns
  were allow-listed in the API configuration.

#### Examples

**Date range filtering** - Get all events between two dates:
```
GET /api/records/v1/events?filter[date][$gte]=2025-01-01&filter[date][$lte]=2025-12-31
```

**Numeric range** - Find products within a price range:
```
GET /api/records/v1/products?filter[price][$gte]=10.00&filter[price][$lt]=50.00
```

For a more complex example, to query the top-3 ranked movies with a watch time below 2 hours
and "love" in their description:

import listDartCode from "@examples/record_api_dart/lib/src/list.dart?raw";
import listTsCode from "@examples/record_api_ts/src/list.ts?raw";
import listSwiftCode from "@examples/record_api_swift/Sources/RecordApiDocs/List.swift?raw";
import listKotlinCode from "@examples/record_api_kotlin/src/main/kotlin/List.kt?raw";
import listDotnetCode from "@examples/record_api_dotnet/List.cs?raw";
import listRustCode from "@examples/record_api_rs/src/list.rs?raw";
import listGoCode from "@examples/record_api_go/list.go?raw";
import listPyCode from "@examples/record_api_py/record_api_py/list.py?raw";
import listCurlCode from "@examples/record_api_curl/list.sh?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={listTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={listDartCode} />
  </TabItem>

  <TabItem label="Swift">
    <Code lang="swift" code={listSwiftCode} />
  </TabItem>

  <TabItem label="Kotlin">
    <Code lang="kotlin" code={listKotlinCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={listDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={listRustCode} />
  </TabItem>

  <TabItem label="Go">
    <Code lang="go" code={listGoCode} />
  </TabItem>

  <TabItem label="Python">
    <Code lang="python" code={listPyCode} />
  </TabItem>

  <TabItem label="curl">
    <Code lang="sh" frame="code" code={listCurlCode} />
  </TabItem>
</Tabs>

### Subscribe

The streaming subscribe endpoints lets you listen for changes to tables backing
an API or specific records given their id. Change events can be insertions,
updates, and deletions.

import subscribeDartCode from "@examples/record_api_dart/lib/src/subscribe.dart?raw";
import subscribeTsCode from "@examples/record_api_ts/src/subscribe.ts?raw";
import subscribeRustCode from "@examples/record_api_rs/src/subscribe.rs?raw";
import subscribeDotnetCode from "@examples/record_api_dotnet/Subscribe.cs?raw";

<Tabs>
  <TabItem label="JS/TS">
    <Code lang="ts" code={subscribeTsCode} />
  </TabItem>

  <TabItem label="Dart">
    <Code lang="dart" code={subscribeDartCode} />
  </TabItem>

  <TabItem label="C#/.NET">
    <Code lang="cs" code={subscribeDotnetCode} />
  </TabItem>

  <TabItem label="Rust">
    <Code lang="rust" code={subscribeRustCode} />
  </TabItem>
</Tabs>

### Schema

The schema endpoint allows for reading the APIs JSON schema definition. This
can be useful for driving external code generation or introspection in general.


## File Uploads

Record APIs can also support file uploads and downloads. There's some special
handling in place so that only metadata is stored in the underlying table while
the actual files are kept in an object store.

By adding a `TEXT` column with a `CHECK(jsonschema('std.FileUpload'))`
constrained to your table definition, you instruct TrailBase to store file
metadata as defined by the "std.FileUpload" JSON schema while keeping the contents
in a separate object store.
Files can then be upload by sending their contents as part of your JSON
requests or `multipart/form-data` POST requests.
Downloading files is slightly different, since reading the column through
record APIs will only yield the metadata. There's a dedicated GET API endpoint
for file downloads:
<code>
{apiPath({name: recordApiNamePlaceholder, suffix:`${recordApiIdPlaceholder}/file/<column_name>`})}
</code>

### S3 Integration

export const s3StorageConfigUrl = githubCodeReference({ path: "crates/core/proto/config.proto", match: "message S3StorageConfig"});

By default, TrailBase will keep the object store on the local file system under
`<traildepot>/uploads`.
Alternatively, an S3 bucket can be set up in the <a href={s3StorageConfigUrl}>configuration</a>.
It's not yet accessible through the admin dashboard.
If you need support for
[other storage backends](https://docs.rs/object_store/latest/object_store/#available-objectstore-implementations),
let us know.


## Custom JSON Schemas

Akin to `std.FileUpload` above, you can register your own nested JSON schemas
to be used with column `CHECK`s.
For now, the dashboard only allows viewing all registered JSON schemas, however
you can register schemas using the configuration:

```json
schemas: [
  {
    name: "simple_schema"
    schema:
      '{'
      '  "type": "object",'
      '  "properties": {'
      '    "name": { "type": "string" },'
      '    "obj": { "type": "object" }'
      '  },'
      '  "required": ["name"]'
      '}'
  }
]
```

Once registered, schemas can be added as column constraints:

```sql
CREATE TALE test (
    simple    TEXT CHECK(jsonschema('simple_schema')),

    -- ...
) STRICT;
```

When generating new client-side bindings for a table or view with such nested
schemas, they will be included ensuring type-safety all the way to the
client-side APIs.

{/*

## Tangent: Querying JSON

Independent of type-safety and Record APIs,
[SQLite](https://www.sqlite.org/json1.html) has first-class support for
querying nested properties of columns containing JSON in textual or binary
format[^4].
For example, given a table:

```sql
CREATE TABLE items (json TEXT NOT NULL);

INSERT INTO items (json) VALUES ('{"name": "House", "color": "blue"}');
INSERT INTO items (json) VALUES ('{"name": "Tent", "color": "red"}');
```

You can query the names of red items:

```sql
SELECT
  json->>'name' AS name
FROM
  items
WHERE
  json->>'color' = 'red';
```

Note that this requires SQLite to scan all rows and deserialize the JSON.
Instead, storing the color of items in a separate, indexed column and filter
on it would be a lot more efficient.
Yet, using JSON for complex structured or denormalized data can be powerful
addition to your toolbox.

*/}

---

[^1]:
    By default, SQLite is not strictly typed. Column types merely express
    affinities  unless the table is explicitly created as `STRICT`.

[^2]:
    Views are more tricky to type strictly being the result of an arbitrary
    `SELECT` statement. For columns that are mapped 1:1 from a `STRICT`ly typed
    `TABLE` the type can be infered. For everything else you can use
    `CAST(col AS <TYPE>)` expressions to assert the types of computed columns.

[^3]:
    There's also a few other endpoints, e.g. for downloading files, which will
    be described further down in the docs.

[^4]:
    Record APIs only support textual JSON. Binary JSON is more compact and more
    efficient to parse, however its actual encoding is internal to SQLite and
    thus opaque to TrailBase.
